import json
from pathlib import PurePath
from typing import Literal, Iterable, Tuple, Union, List, Optional

from sqlalchemy import Column, PrimaryKeyConstraint, Index, select, delete, union_all
from sqlalchemy.dialects.sqlite import INTEGER, TEXT, DATETIME
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy.orm import declarative_base

from my_webdav.config import app_setting
from ..client_115.schemas import FileOrFolder, FileResp, FolderResp

logger = app_setting.app_log

Base = declarative_base()


class CacheData(Base):
    __tablename__ = 'cache_data'

    uid = Column(INTEGER, comment='115网盘用户id')
    dir_path = Column(TEXT, comment='115网盘中该资源对应的目录路径')
    full_path = Column(TEXT, comment='115网盘中该资源对应的全路径')
    res_id = Column(INTEGER, comment='115网盘中该资源对应的id')
    res_info = Column(TEXT, comment='115网盘中该资源对应的详细信息')
    update_time = Column(DATETIME, comment='该条缓存的更新时间')

    __table_args__ = (
        PrimaryKeyConstraint(res_id),
        Index('dir_path_idx', dir_path),
        Index('full_path_idx', full_path)
    )


class CacheLayer(object):
    def __init__(self, session: AsyncSession):
        self.session = session

    async def list_member(self, path: PurePath, depth: Literal[0, 1]) -> List[FileOrFolder]:
        path_str = path.as_posix()
        query_self = (
            select(CacheData.res_info)
            .where(CacheData.full_path == path_str)
        )
        if depth == 0:
            stmt = query_self

        else:
            query_child = (
                select(CacheData.res_info)
                .where(CacheData.dir_path == path_str)
            )
            # 保证full_path == path的记录在第一个
            stmt = union_all(query_self, query_child)

        res = await self.session.execute(stmt)
        members = map(
            lambda x: FileResp(**x) if x.get('fid') is not None else FolderResp(**x),
            [json.loads(r.res_info) for r in res.all()]
        )
        return list(members)

    async def write_cache(self, data: Iterable[CacheData]):
        self.session.add_all(data)
        await self.session.commit()

    async def delete_cache(self, conds: Iterable[Tuple[str, Union[str, int]]]) -> int:
        conds_col = [
            getattr(CacheData, col_name) == col_value for col_name, col_value in conds
        ]
        stmt = (
            delete(CacheData)
            .where(*conds_col)
            .execution_options(synchronize_session=False)
        )
        cursor = await self.session.execute(stmt)
        return cursor.rowcount

    async def delete_res(self, res_path: PurePath, is_dir: bool):
        """
        删除缓存中某个资源: 若为目录则删除该记录和其子成员.
        :param is_dir:
        :param res_path:
        :return:
        """
        logger.debug(f"delete res in cache: {res_path}")
        path = res_path.as_posix()
        cond = CacheData.full_path.like(f"{path}%") if is_dir else CacheData.full_path == path
        stmt = (
            delete(CacheData)
            .where(cond)
            .execution_options(synchronize_session=False)
        )
        cursor = await self.session.execute(stmt)
        # await self.session.commit()
        return cursor.rowcount

    async def delete_all(self):
        stmt = (
            delete(CacheData)
            .execution_options(synchronize_session=False)
        )
        cursor = await self.session.execute(stmt)
        return cursor.rowcount

    async def path_to_id(self, path: PurePath) -> Optional[int]:
        stmt = (
            select(CacheData.res_id).where(CacheData.full_path == path.as_posix())
        )
        res = await self.session.execute(stmt)
        res_id = res.scalar()
        return res_id
